﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Transactions;

using System.Data;
using System.Data.OleDb;

namespace DataAccessLayer
{


    public class OleDbDatabase
    {
        public enum MappingTaskID : int
        {
            Default = 0,
            MarketingProgram = 109,
            MarketingCustomer = 111,
            
            GCGBCommand = 121,
            GCGBPlaster = 131,
            GCGBStaple = 132,
            GCGBClipping = 133,
            GCGBPaste = 134

            
        }

        public static string GetMappingSheetName(MappingTaskID mappingTaskID)
        {
            switch (mappingTaskID)
            {
                case MappingTaskID.MarketingProgram:
                    return "Import Marketing Program";
                case MappingTaskID.MarketingCustomer:
                    return "Import Customer";
                case MappingTaskID.GCGBCommand:
                    return "Import Command";
                case MappingTaskID.GCGBPlaster:
                    return "Import Plaster";
                case MappingTaskID.GCGBClipping:
                    return "Import Clipping";
                case MappingTaskID.GCGBStaple:
                    return "Import Staple";
                case MappingTaskID.Default:
                    return "";
                default:
                    return "";
            }
        }

        public static DataTable OpenExcelSheet(string excelFile, string sheetName, string querySelect, string queryOrderBy)
        {
            try
            {
                using (TransactionScope suppressScope = new TransactionScope(TransactionScopeOption.Suppress)) //Do non-transactional work here
                {
                    OleDbConnection excelConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"); //HDR=NO | HDR=YES ---- Header row -- IMEX=1: Treating all data as text (safer way to retrieve data for mixed data columns) //http://www.connectionstrings.com/excel-2007#ace-oledb-12-0
                    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(querySelect + " FROM [" + sheetName + "$] " + queryOrderBy, excelConnection);
                    DataTable dataTable = new DataTable();
                    dataAdapter.Fill(dataTable);

                    return dataTable;
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        public static DataTable OpenExcelSheet(MappingTaskID mappingTaskID, string excelFile)
        {
            try
            {
                string querySelect = " SELECT 1 AS NoUseField "; string queryOrderBy = "";

                DataTable ExcelMapTable = SQLDatabase.GetDataTable("SELECT ColumnName, ColumnMappingName, OrderByID FROM ListColumnMapping WHERE MappingTaskID = " + (int)mappingTaskID + " ORDER BY OrderByID");

                if (ExcelMapTable.Rows.Count > 0)
                {
                    foreach (DataRow dataRow in ExcelMapTable.Rows)
                    {
                        querySelect = querySelect + ", " + "[" + dataRow["ColumnMappingName"] + "] AS " + dataRow["ColumnName"];
                        if ((int)dataRow["OrderByID"] != 0) queryOrderBy = queryOrderBy + (queryOrderBy == "" ? " ORDER BY " : ", ") + dataRow["ColumnName"];
                    }
                }

                return OleDbDatabase.OpenExcelSheet(excelFile, OleDbDatabase.GetMappingSheetName(mappingTaskID), querySelect, queryOrderBy);
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }
    }
}
